Defining and using dynamic lookups
The dynamic lookup object is parallel in many ways to the dynamic combo, providing an alternative visualization of the list of valid choices for a field. The lookup is the best choice when you need to display many possible choices. The lookup brings up a separate window where a browser with a list of values displayed. Given the filtering and record batching capabilities of Progress Dynamics, a user can select a value efficiently from a list of any size.
Because the lookup is similar to the combo in many respects, it is described here as a variant, so as not to repeat all of the property sheet field descriptions and other details. So, be sure to read the "Defining and using dynamic combos" section and the "Saving the combo" section before reading this section. Unlike the combo, which does have static counterparts, there is no standard static lookup object in Progress Dynamics. All of this description applies just to the dynamic object (simply referred to as a lookup).
|
To add a lookup to a viewer: |
- Right-click on the SmartDataField icon on the AppBuilder palette, then select Dynamic Lookup from the pop-up list.
- Position the cursor over the field to be replaced by a lookup, and select it with the left mouse button.
- You can select a lookup from the list, or choose Create New SDF. The SmartDataField Maintenance utility window appears, as shown:
- Fill in the top fields, enter the base query string, and choose Refresh. The query can reference up to 10 tables. This step initializes the lower portion of the Dynamic SmartDataField Maintenance window with defaults for many of the fields, based on the database tables in the query. The filled-in Dynamic SmartDataField Maintenance window for a Customer Number Lookup looks like this:
This example involves building an Order Viewer for the Sports2000 database. The user must assign each order to a valid existing Customer. The lookup is on the CustNum field so that the user can select a Customer by browsing through a list that includes the Name and other useful fields. The viewer displays the CustNum field. There is some space to the right of CustNum field where the Lookup button and Customer Name field can be shown, as follows:
When you drop the lookup onto it, the Choose Existing SmartDataField window appears, as shown:
The following subsections describe fields and values in the Dynamic SmartDataField Maintenance window that are different from those for the combo.
Rows to batch
An attribute of lookups not relevant to the combo is the number of database rows to send across from server to client in one batch. If there are many possible values for the foreign key field the lookup represents, it would not be practical to populate a client-side temp-table with all of them before displaying the list to the user; this might take ages. So records are loaded into the temp-table and sent to the client in batches. The default value of 200 is somewhat arbitrary, but a reasonable starting figure. If you expect the user typically to filter the lookup value list before choosing a value, then you might set this number to be significantly smaller than 200, since filtering the list repopulates the lookup browser. Decreasing the Rows To Batch value improves run-time performance somewhat.
On the other hand, if you know that the total number of possible values (total number of Customers in the Customer table, for example) is a number greater than but not too much greater than 200, then you might set Rows To Batch high enough to retrieve them all at once. Otherwise, the framework retrieves one batch of rows, and other batches as needed, if the user scrolls around or repositions in the list.
Browser fields
As with the combo, the Dynamic SmartDataField Maintenance window shows a list of all fields in the tables in the Base Query. The list of fields in this browser, and how you use them, is somewhat different for the lookup.
As with the combo, you can select one or more fields to display to the user. In the case of the lookup, these fields will be columns in a browser. To select a field to be added to the browser, give it a browser sequence number. The example in Step 4 above has been sorted (by clicking on the Browser Seq. column label) to show that five fields are selected to display in the browser. Because the browser is displayed in a separate window, and because the user can scroll it horizontally, you can add as many fields to the browser as you like., Keep in mind that this is a mechanism for selecting a foreign key value, so you should choose fields that would help the user select the right record quickly.
Note: The browser is based on a temp-table built dynamically from the field names selected. If your base query joins two or more tables, you cannot include more than one field in the lookup browser with the same field name but a different table name.Linked fields
In addition to showing fields from the related table in the lookup browse window, you can elect to display one or more of these fields in the viewer itself. This makes the viewer’s display more effective. It also causes the related descriptive fields to be seen as soon as the viewer is entered, not just when the lookup is used to set or change the foreign key field value.
To select a field as a linked field, click in the Link Field cell for that field name and change NO to YES. This example uses the Customer.Name field to display in the viewer with the value taken from the lookup browser.
Linked widgets
Normally, for each Linked Field you select, you will want to map that field to a local variable in the viewer where the field’s value will be displayed. Enter the name of the local variable in the Linked Widget browser cell for that field. Then when you return to the viewer design window from the property sheet, you must define that fill-in in the viewer.
|
|
To define the viewer: |
In the example, a character variable is defined, called cCustomerName, and placed next to the CustNum Lookup.
LookupComplete. You would only need to do this if your lookup required special formatting or some other kind of special handling of the Linked Field value.
Override label and override format
You can change the column label and display format for the fields selected to be shown in the lookup browser.
Note: If you translated the label using the Progress Dynamics Translation utility, you cannot override the translated label here.Displayed field
As for combos, the Key Field is chosen to be the field whose value is assigned to the external field on completion of the lookup operation. You can choose another field value to show in the viewer in place of the Key Field. This field is called the Displayed Field. The Displayed Field can be the same as the Key Field, but it can also be different, if the Key Field is not a meaningful value to display to the user. In the example, the Order Viewer displays the Customer Name as a Linked Widget in addition to the Customer Number as Displayed Field. If you wanted to eliminate the display of the Customer Number altogether, you could designate the Name as the Displayed Field and not bother defining a Linked Widget and variable name for it at all.
The lookup supports entry of the Displayed Field for resolution of the lookup without calling up the browse window. It also does auto-completion of a unique partial entry for the field. You should use a sensible indexed field as the Key Field wherever possible.
Parent field and parent filter query
These entries allow you to define a parent-child relationship in a viewer. Some other Lookup, Combo, or other field is expected to provide one or more parent values used to filter a dependent child lookup, to display only values valid for that parent value. This works exactly the same as for combos.
Browser title
This is the title to be displayed in the Lookup browse window. It should describe the data that is being looked up, for example “Customer Lookup.”
Maintenance SDO and maintenance object
In some cases, you might want to allow the user to enter a missing record in the parent table of this foreign key relationship, or to edit an existing record, directly from the Lookup. For example, if the user was entering an Order for a Customer and discovered that the Customer record had not yet been created, you might want to allow the user to create it here, without the user having to bring up a separate Customer Maintenance window from some other menu.
If this is the case, you must enter the name of the SDO used to maintain that parent table in the Maintenance SDO field. You must also enter the name of the maintenance window to launch to create or edit the record in the Maintenance Object field.
If you fill in these fields, then a toolbar with an Update button band is added to the Lookup browse window, so that the user can select Add, Copy, or Edit to change the parent table on the spot.
When you exit the property sheet, you must remember to define the variables for any linked widgets you defined for the lookup. In the example shown in Figure 7–2, a variable is defined for the Customer Name field, and placed next to the CustNum Lookup. The label for the Name field is not included, so that the fill-in is displayed right up against the Lookup. This is strictly a visual choice.
Changing labels in viewers
You can change labels for fields in a SmartDataViewer in several ways:
- Use the Data Field Maintenance tool to make a global change to the label. Changes made in this tool will not be immediately reflected in open viewers. Close and reopen the viewer to see your changes.
- Override the data field master with the Override Label property. Changes made here apply even if the data field master is changed at a subsequent time.
- Right-click the field and choose Edit Datafield Master. Changes made in this way will take effect only if there are no overrides on the field.